﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Newtonsoft.Json;

namespace ExcelJsonParser
{
    class JsonWorkSheetTable
    {
        const string ArrayRowHeader = "Row Header";
        private const string TableNameForJsonObject = "JsonObject";
        private const string TableNameForJsonArray = "JsonArray";
        private Worksheet ws = null;
        private enum UpdateAction { NoUpdate, Column, Row, RowHeader };
        public JsonWorkSheetTable(Range startCell,object javaObject)
        {
            foreach (ListObject listObj in startCell.Parent.ListObjects)
            {
                listObj.Delete();
            }
            switch (javaObject.GetType().Name)
            {
                case "JavaScriptArray":
                    JavaScriptArray jsArray = javaObject as JavaScriptArray;
                    FillJsonArray(startCell, jsArray);
                    break;
                case "JavaScriptObject":
                    JavaScriptObject jsObject = javaObject as JavaScriptObject;
                    FillJsonObject(startCell, jsObject);
                    break;
                default:
                    FillVariant(startCell, javaObject);
                    break;
            }
            ws = startCell.Parent;
            ws.Change -= new DocEvents_ChangeEventHandler(ws_Change);
            ws.Change += new DocEvents_ChangeEventHandler(ws_Change);
        }

        public JsonWorkSheetTable(ListObject table)
        {
            ws = table.Parent;
            ws.Change -= new DocEvents_ChangeEventHandler(ws_Change);
            ws.Change += new DocEvents_ChangeEventHandler(ws_Change);
        }
        Boolean GeneratingJson = false;
        void ws_Change(Range Target)
        {
            Worksheet ws = Target.Parent;
            if (GeneratingJson == false)
            {
                    if (ws.ListObjects.Count < 1)
                    {
                        return;
                    }
                    Range tableRange = ws.ListObjects.Item[1].Range;
                    int x1 = 0;
                    int x2 = 0;
                    int y1 = 0;
                    int y2 = 0;
                    if ((tableRange.Column + tableRange.Columns.Count - 1) >= (Target.Column + Target.Columns.Count - 1))
                    {

                    }
                    int tableX1 = tableRange.Column;
                    int tableX2 = tableRange.Column + tableRange.Columns.Count - 1;
                    int tableY1 = tableRange.Row;
                    int tableY2 = tableRange.Row + tableRange.Rows.Count - 1;
                    int targetX1 = Target.Column;
                    int targetX2 = Target.Column + Target.Columns.Count - 1;
                    int targetY1 = Target.Row;
                    int targetY2 = Target.Row + Target.Rows.Count - 1;
                    if (tableX1 >= targetX1)
                    {
                        x1 = tableX1;
                    }
                    else
                    {
                        x1 = targetX1;
                    }
                    if (tableX2 <= targetX2)
                    {
                        x2 = tableX2;
                    }
                    else
                    {
                        x2 = targetX2;
                    }
                    if (tableY1 >= targetY1)
                    {
                        y1 = tableY1;
                    }
                    else
                    {
                        y1 = targetY1;
                    }
                    if (tableY2 <= targetY2)
                    {
                        y2 = tableY2;
                    }
                    else
                    {
                        y2 = targetY2;
                    }
                    if (x1 > x2 || y1 > y2)
                    {
                        return;
                    }
                    int rowStartIndex = y1 - tableY1;
                    int rowEndIndex = y2 - tableY1;
                    UpdateAction updateAction = UpdateAction.NoUpdate;
                    if (x1 == tableX1 && x2 == tableX1 && y1 != tableY1)
                    {
                        updateAction = UpdateAction.RowHeader;
                    }
                    else
                    {
                        if (y1 == tableY1)
                        {
                            updateAction = UpdateAction.Column;
                        }
                        else
                        {
                            if (x1 > tableX1 && y1 > tableY1)
                            {
                                updateAction = UpdateAction.Row;
                            }
                        }
                    }
                    UpdateTable(rowStartIndex, rowEndIndex, ws, updateAction);
            }
        }

        private void UpdateTable(int rowStartIndex, int rowEndIndex, Worksheet ws, UpdateAction updateAction)
        {
            GeneratingJson = true;
            try
            {
                switch (updateAction)
                {
                    case UpdateAction.Column:
                        RefreshTable(ws);
                        break;
                    case UpdateAction.Row:
                        RefreshRows(ws, rowStartIndex, rowEndIndex);
                        break;
                    case UpdateAction.RowHeader:
                        RefreshByRowHeader(ws, rowStartIndex, rowEndIndex);
                        break;
                    default:
                        break;
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                GeneratingJson = false;
            }
        }

        private void RefreshByRowHeader(Worksheet ws, int rowStartIndex, int rowEndIndex)
        {
            List<string> columnList = new List<string>();
            columnList.Add(ArrayRowHeader);
            foreach (ListColumn column in ws.ListObjects.Item[1].ListColumns)
            {
                columnList.Add(column.Name);
            }
            foreach (ListRow row in ws.ListObjects.Item[1].ListRows)
            {
                if (row.Index >= rowStartIndex && row.Index <= rowEndIndex)
                {
                    object jsObject = JsonWorkCell.GetJsonValueFromCell(row.Range[1, 1]);
                    if (jsObject is JavaScriptObject)
                    {
                        JavaScriptObject jsonObj = jsObject as JavaScriptObject;
                        foreach (string key in jsonObj.Keys)
                        {
                            if (columnList.Contains(key) == false)
                            {
                                ListColumn newColumn = ws.ListObjects.Item[1].ListColumns.Add();
                                newColumn.Name = key;
                                columnList.Add(key);
                            }
                        }
                        foreach (string column in columnList)
                        {
                            if (columnList.IndexOf(column) == 0)
                            {
                                continue;
                            }
                            Range valueCell = row.Range[1, columnList.IndexOf(column)];
                            //string tableColumnName = column;//ws.ListObjects.Item[1].ListColumns.Item[columnList.IndexOf(column)].Name;
                            if (jsonObj.ContainsKey(column))
                            {
                                JsonWorkCell.SetCellByJson(valueCell, jsonObj[column]);
                            }
                            else
                            {
                                JsonWorkCell.SetCellByJson(valueCell, JsonWorkCell.jsonUndefined);
                            }
                        }
                    }
                    else
                    {
                        foreach (ListColumn column in ws.ListObjects.Item[1].ListColumns)
                        {
                            if (column.Index == 1)
                            {
                                continue;
                            }
                            Range valueCell = row.Range[1, column.Index];
                            JsonWorkCell.SetCellByJson(valueCell, JsonWorkCell.jsonUndefined);
                        }
                    }
                }
            }
        }

        private void RefreshRows(Worksheet ws, int rowStartIndex, int rowEndIndex)
        {
            foreach (ListRow row in ws.ListObjects.Item[1].ListRows)
            {
                if (row.Index >= rowStartIndex && row.Index <= rowEndIndex)
                {
                    JavaScriptObject jsObj = new JavaScriptObject();
                    foreach (ListColumn column in ws.ListObjects.Item[1].ListColumns)
                    {
                        if (column.Index == 1)
                        {
                            continue;
                        }
                        string jsonKey = ws.ListObjects.Item[1].ListColumns.Item[column.Index].Name;
                        Range valueCell = row.Range[1, column.Index];
                        object jsonValue = JsonWorkCell.GetJsonValueFromCell(valueCell);
                        if (jsonValue is JsonUndefined)
                        {
                            continue;
                        }
                        if (jsObj.ContainsKey(jsonKey) == false)
                        {
                            jsObj.Add(jsonKey, jsonValue);
                        }
                        else
                        {
                            jsObj[jsonKey] = jsonValue;
                        }
                    }
                    Range rowHeaderCell = row.Range[1, 1];
                    if (jsObj.Keys.Count == 0)
                    {
                        continue;
                    }
                    JsonWorkCell.SetCellByJson(rowHeaderCell, jsObj);
                }
            }
        }

        private void RefreshTable(Worksheet ws)
        {
            Range arrayRowHeaderCell = ws.ListObjects.Item[1].ListColumns[1].Range[1];
            arrayRowHeaderCell.Value = ArrayRowHeader;
            foreach (ListRow row in ws.ListObjects.Item[1].ListRows)
            {
                JavaScriptObject jsObj = new JavaScriptObject();
                foreach (ListColumn column in ws.ListObjects.Item[1].ListColumns)
                {
                    if (column.Index == 1)
                    {
                        continue;
                    }
                    string jsonKey = ws.ListObjects.Item[1].ListColumns.Item[column.Index].Name;
                    Range valueCell = row.Range[1, column.Index];
                    object jsonValue = JsonWorkCell.GetJsonValueFromCell(valueCell);
                    if (jsonValue is JsonUndefined)
                    {
                        continue;
                    }
                    if (jsObj.ContainsKey(jsonKey) == false)
                    {
                        jsObj.Add(jsonKey, jsonValue);
                    }
                    else
                    {
                        jsObj[jsonKey] = jsonValue;
                    }
                }
                Range rowHeaderCell = row.Range[1, 1];
                string tempstr = rowHeaderCell.Value;
                if (jsObj.Keys.Count == 0)
                {
                    continue;
                }
                JsonWorkCell.SetCellByJson(rowHeaderCell, jsObj);
            }
        }

        private void FillJsonArray(Range startCell, JavaScriptArray jsArray)
        {
            //Init Columns Header
            Worksheet ws = startCell.Parent;
            int column = startCell.Column + 1;
            int row = startCell.Row;
            List<string> columnList = new List<string>();
            ws.Cells[startCell.Row, startCell.Column] = ArrayRowHeader;
            columnList.Add(ArrayRowHeader);
            foreach (var item in jsArray)
            {
                if (item is JavaScriptObject)
                {
                    JavaScriptObject jsObj = item as JavaScriptObject;
                    foreach (string key in jsObj.Keys)
                    {
                        if (columnList.Contains(key) == false)
                        {
                            columnList.Add(key);
                            ws.Cells[row, column] = key;
                            column++;
                        }
                    }
                }
            }
            //Fill Data
            row = startCell.Row + 1;
            foreach (var item in jsArray)
            {
                column = startCell.Column;
                if (item is JavaScriptObject)
                {
                    JavaScriptObject jsObj = item as JavaScriptObject;
                    foreach (string key in columnList)
                    {
                        if (ArrayRowHeader.Equals(key))
                        {
                            //object value = null;
                            //value = JavaScriptConvert.SerializeObject(jsObj);
                            JsonWorkCell.SetCellByJson(ws.Cells[row, startCell.Column], jsObj);
                        }
                        else
                        {

                            if (jsObj.ContainsKey(key))
                            {
                                object value = null;
                                jsObj.TryGetValue(key, out value);
                                JsonWorkCell.SetCellByJson(ws.Cells[row, column], value);
                            }
                            else
                            {
                                JsonWorkCell.SetCellByJson(ws.Cells[row, column], JsonWorkCell.jsonUndefined);
                            }
                        }
                        column++;
                    }
                }
                else
                {
                    JsonWorkCell.SetCellByJson(ws.Cells[row, startCell.Column], item);
                }
                row++;
            }
            Range tableRangeStart = ws.Cells[startCell.Row, startCell.Column];
            Range tableRangeEnd = ws.Cells[startCell.Row + jsArray.Count, startCell.Column + columnList.Count - 1];
            Range tableRange = ws.Range[tableRangeStart, tableRangeEnd];
            try
            {
                ListObject jsonTable = ws.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, tableRange, Type.Missing, XlYesNoGuess.xlYes, Type.Missing);
                jsonTable.Name = "JsonArray"+ Guid.NewGuid();
            }
            catch (Exception)
            {
            }
        }

        private void FillJsonObject(Range startCell, JavaScriptObject jsObject)
        {
            Worksheet ws = startCell.Parent;
            //Init Columns Header
            int column = startCell.Column + 1;
            int row = startCell.Row;
            List<string> columnList = new List<string>();
            ws.Cells[startCell.Row, startCell.Column] = ArrayRowHeader;
            columnList.Add(ArrayRowHeader);
            foreach (string key in jsObject.Keys)
            {
                if (columnList.Contains(key) == false)
                {
                    columnList.Add(key);
                    ws.Cells[row, column] = key;
                    column++;
                }
            }
            //Fill Data
            row = startCell.Row + 1;
            column = startCell.Column;
            foreach (string key in columnList)
            {
                if (ArrayRowHeader.Equals(key))
                {
                    //object value = null;
                    //value = JavaScriptConvert.SerializeObject(jsObject);
                    JsonWorkCell.SetCellByJson(ws.Cells[row, column], jsObject);
                }
                else
                {
                    object value = null;
                    jsObject.TryGetValue(key, out value);
                    JsonWorkCell.SetCellByJson(ws.Cells[row, column], value);
                }
                column++;
            }
            Range tableRangeStart = ws.Cells[startCell.Row, startCell.Column];
            Range tableRangeEnd = ws.Cells[startCell.Row + 1, startCell.Column + columnList.Count - 1];
            Range tableRange = ws.Range[tableRangeStart, tableRangeEnd];
            try
            {
                ListObject jsonTable = ws.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, tableRange, Type.Missing, XlYesNoGuess.xlYes, Type.Missing);
                jsonTable.Name = "JsonObject" + Guid.NewGuid();
            }
            catch (Exception)
            {
            }
        }

        private void FillVariant(Range startCell, object javaObject)
        {
            Worksheet ws = startCell.Parent;
            //Init Columns Header
            int column = startCell.Column ;
            int row = startCell.Row;
            ws.Cells[startCell.Row, startCell.Column] = ArrayRowHeader;
            Range valueCell = ws.Cells[startCell.Row + 1, startCell.Column];
            JsonWorkCell.SetCellByJson(valueCell,javaObject);
            Range tableRangeStart = ws.Cells[startCell.Row, startCell.Column];
            Range tableRangeEnd = ws.Cells[startCell.Row + 1, startCell.Column];
            Range tableRange = ws.Range[tableRangeStart, tableRangeEnd];
            try
            {
                ListObject jsonTable = ws.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, tableRange, Type.Missing, XlYesNoGuess.xlYes, Type.Missing);
                jsonTable.Name = "JsonObject" + Guid.NewGuid();
            }
            catch (Exception)
            {
            }
        }

        public static object GetOutputJson(Worksheet ws)
        {
            object returnJavaObject = JsonWorkCell.jsonUndefined;
            JavaScriptArray jsArray = new JavaScriptArray();
            //JavaScriptObject jsObj = null;
            try
            {
                if (ws.ListObjects.Count > 0)
                {
                    if (ws.ListObjects.Item[1].Name.StartsWith(TableNameForJsonArray))
                    {
                        foreach (ListRow row in ws.ListObjects.Item[1].ListRows)
                        {
                            Range cell = (Range)ws.ListObjects.Item[1].ListRows[1].Range[row.Index, 1];
                            object value = JsonWorkCell.GetJsonValueFromCell(cell);
                            jsArray.Add(value);
                        }
                        returnJavaObject = jsArray;
                    }
                    if (ws.ListObjects.Item[1].Name.StartsWith(TableNameForJsonObject))
                    {
                        returnJavaObject = JsonWorkCell.GetJsonValueFromCell((ws.ListObjects.Item[1].ListRows[1].Range[1, 1]));
                    }
                }
            }
            catch (Exception)
            {
            }
            return returnJavaObject;
        }



        internal void RefreshCell(Range range)
        {
            ws_Change(range);
        }
    }
}
